package com.springdemo.annother.parseexcel;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.springdemo.annother.parseexcel.util.PhoneUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelUtil {
  private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

  /**
   * 解析excel
   *
   * @param filePath
   * @return
   */
  public static List<String> parseExcel(String filePath) {
    logger.info("开始解析excel文件");
    // 读取excel文件
    InputStream is = null;
    String fileType = filePath.substring(filePath.lastIndexOf(".") + 1);
    List<String> lists = new ArrayList<>();
    try {
      is = new FileInputStream(filePath);
      // 获取工作薄
      Workbook wb = null;
      // 根据类型解析不同的xml文件
      switch (fileType) {
        case "xls":
          wb = new HSSFWorkbook(is);
          break;
        case "xlsx":
          wb = new XSSFWorkbook(is);
          break;
        default:
          return null;
      }
      // 读取第一个工作页sheet
      Sheet sheet = wb.getSheetAt(0);
      // 第一行为标题
      int index = 0;
      for (Row row : sheet) {
        if (index == 0) {
          index++;
          continue;
        }
        //http请求时间太长 暂时只处理前20个测试
          index++;
          if (index==1000){
            break;
        }
        // 获取电话号和城市
        Cell phoneCell = row.getCell(8);
        Cell cityCell = row.getCell(10);
        if (phoneCell != null && cityCell != null) {
          phoneCell.setCellType(Cell.CELL_TYPE_STRING);
          String city =
              cityCell.getStringCellValue().replaceAll("\\s*", "").replaceAll("\r|\n", "").trim();
          String phone =
              phoneCell.getStringCellValue().replaceAll("\\s*", "").replaceAll("\r|\n", "").trim();
          String parsePhone = PhoneUtil.checkPhoneNumber(phone);
          int index2 = parsePhone.indexOf("-");
          String quhao = "";
          if (index2 > 0) {
            quhao = parsePhone.substring(0, index2);
          }
          System.out.println(
              "用户所在城市：{"+ city+ "}"+ "电话：{"+ phone+ "},用户电话：{"+ parsePhone+ "},区号是：{"+ quhao+ "}");
          String realQuhao = PhoneUtil.getQuhao(city.replace("市", ""));
          if (!phone.equals(parsePhone)){
              //修改电话格式的文件标记
              phoneCell.setCellValue(parsePhone);
              CellStyle cellStyle = wb.createCellStyle();
              cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
              cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
              System.out.println("区号：" + quhao + "真实区号：" + realQuhao + "不一致！");
              phoneCell.setCellStyle(cellStyle);
          }

          //先判断是否是座机号码  是座机号码再处理
            if(!PhoneUtil.checkIsPhone(parsePhone)){
                if (!quhao.equals(realQuhao)) {
                    phoneCell.setCellValue(parsePhone.replace(quhao+"-",realQuhao+"-"));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
                    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    System.out.println("区号：" + quhao + "真实区号：" + realQuhao + "不一致！");
                    phoneCell.setCellStyle(cellStyle);
                }
            }
        }
      }

      File file = new File("D:\\excel\\t_poi_shop_source2.xlsx");
      OutputStream outputStream = new FileOutputStream(file);
      wb.write(outputStream);
      outputStream.flush();
      outputStream.close();

    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      try {
        if (is != null) {
          is.close();
        }
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    return lists;
  }
}
